1

Import the ESRI shapefile of German districts and the district attribute table. Join the two data frames, transform the CRS to EPSG:3035 and check your changes.

You need to rename one of the id variables or adjust your join accordingly (AGS = district_id).

solution 

# load libraries
library(sf)
library(dplyr)

# Import data
german_districts <- 
  sf::read_sf("./data/VG250_KRS.shp") %>% 
  dplyr::rename(district_id = AGS)

attributes_districts <- readr::read_csv("./data/attributes_districts.csv") 
## Rows: 411 Columns: 5
## ── Column specification ─────────────────────────────────
## Delimiter: ","
## chr (1): district_id
## dbl (4): population, death_rate, death7_lk, afd_votes...
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
# Join data and transform
german_districts_enhanced <- 
  german_districts %>% 
  dplyr::left_join(attributes_districts, by = "district_id") %>% 
  sf::st_transform(3035)

# Check
sf::st_crs(german_districts_enhanced)
## Coordinate Reference System:
##   User input: EPSG:3035 
##   wkt:
## PROJCRS["ETRS89-extended / LAEA Europe",
##     BASEGEOGCRS["ETRS89",
##         ENSEMBLE["European Terrestrial Reference System 1989 ensemble",
##             MEMBER["European Terrestrial Reference Frame 1989"],
##             MEMBER["European Terrestrial Reference Frame 1990"],
##             MEMBER["European Terrestrial Reference Frame 1991"],
##             MEMBER["European Terrestrial Reference Frame 1992"],
##             MEMBER["European Terrestrial Reference Frame 1993"],
##             MEMBER["European Terrestrial Reference Frame 1994"],
##             MEMBER["European Terrestrial Reference Frame 1996"],
##             MEMBER["European Terrestrial Reference Frame 1997"],
##             MEMBER["European Terrestrial Reference Frame 2000"],
##             MEMBER["European Terrestrial Reference Frame 2005"],
##             MEMBER["European Terrestrial Reference Frame 2014"],
##             ELLIPSOID["GRS 1980",6378137,298.257222101,
##                 LENGTHUNIT["metre",1]],
##             ENSEMBLEACCURACY[0.1]],
##         PRIMEM["Greenwich",0,
##             ANGLEUNIT["degree",0.0174532925199433]],
##         ID["EPSG",4258]],
##     CONVERSION["Europe Equal Area 2001",
##         METHOD["Lambert Azimuthal Equal Area",
##             ID["EPSG",9820]],
##         PARAMETER["Latitude of natural origin",52,
##             ANGLEUNIT["degree",0.0174532925199433],
##             ID["EPSG",8801]],
##         PARAMETER["Longitude of natural origin",10,
##             ANGLEUNIT["degree",0.0174532925199433],
##             ID["EPSG",8802]],
##         PARAMETER["False easting",4321000,
##             LENGTHUNIT["metre",1],
##             ID["EPSG",8806]],
##         PARAMETER["False northing",3210000,
##             LENGTHUNIT["metre",1],
##             ID["EPSG",8807]]],
##     CS[Cartesian,2],
##         AXIS["northing (Y)",north,
##             ORDER[1],
##             LENGTHUNIT["metre",1]],
##         AXIS["easting (X)",east,
##             ORDER[2],
##             LENGTHUNIT["metre",1]],
##     USAGE[
##         SCOPE["Statistical analysis."],
##         AREA["Europe - European Union (EU) countries and candidates. Europe - onshore and offshore: Albania; Andorra; Austria; Belgium; Bosnia and Herzegovina; Bulgaria; Croatia; Cyprus; Czechia; Denmark; Estonia; Faroe Islands; Finland; France; Germany; Gibraltar; Greece; Hungary; Iceland; Ireland; Italy; Kosovo; Latvia; Liechtenstein; Lithuania; Luxembourg; Malta; Monaco; Montenegro; Netherlands; North Macedonia; Norway including Svalbard and Jan Mayen; Poland; Portugal including Madeira and Azores; Romania; San Marino; Serbia; Slovakia; Slovenia; Spain including Canary Islands; Sweden; Switzerland; Türkiye (Turkey); United Kingdom (UK) including Channel Islands and Isle of Man; Vatican City State."],
##         BBOX[24.6,-35.58,84.73,44.83]],
##     ID["EPSG",3035]]
head(german_districts_enhanced, 2)
## Simple feature collection with 2 features and 27 fields
## Geometry type: MULTIPOLYGON
## Dimension:     XY
## Bounding box:  xmin: 4279627 ymin: 3460480 xmax: 4335232 ymax: 3524426
## Projected CRS: ETRS89-extended / LAEA Europe
## # A tibble: 2 × 28
##     ADE    GF   BSG ARS   district_id SDV_ARS GEN   BEZ  
##   <int> <int> <int> <chr> <chr>       <chr>   <chr> <chr>
## 1     4     4     1 01001 01001       010010… Flen… Krei…
## 2     4     4     1 01002 01002       010020… Kiel  Krei…
## # ℹ 20 more variables: IBZ <int>, BEM <chr>, NBD <chr>,
## #   SN_L <chr>, SN_R <chr>, SN_K <chr>, SN_V1 <chr>,
## #   SN_V2 <chr>, SN_G <chr>, FK_S3 <chr>, NUTS <chr>,
## #   ARS_0 <chr>, AGS_0 <chr>, WSK <date>,
## #   DEBKG_ID <chr>, geometry <MULTIPOLYGON [m]>,
## #   population <dbl>, death_rate <dbl>, death7_lk <dbl>,
## #   afd_voteshare_2021 <dbl>

2

We want a first descriptive visual of the distribution of Covid-19 deaths in Mannheim and the surrounding districts. Calculate the number of Covid-19 deaths (death_rate) by population (population) and multiply with 100,000.

Filter the district of Mannheim (district_id == "08222"), find the surrounding districts, and plot Mannheim and its surrounding districts.

You can use the dplyr function sf::bind_rows() to combine the two spatial objects, “Mannheim” and “Mannheim Surroundings”.

solution 

# calculate Covid-19 rate
german_districts_enhanced <-
  german_districts_enhanced %>% 
  dplyr::mutate(covid_deaths_pop = (death_rate / population) * 100000)

# filter Mannheim
mannheim <-
  german_districts_enhanced %>% 
  dplyr::filter(district_id == "08222")

# filter surrounding districts, append with Mannheim data and select the Covid column
mannheim_sur <-
  german_districts_enhanced %>%
  dplyr::filter(lengths(sf::st_touches(., mannheim)) > 0) %>% 
  dplyr::bind_rows(mannheim) %>%   
  dplyr::select(covid_deaths_pop)

# plot  
plot(mannheim_sur)

3

Save your data set of Mannheim and its surrounding districts as an ESRI Shapefile.

solution 

# Export as shapefile
sf::st_write(
  cologne_sur, 
  dsn = "./data/participant_materials/mannheim_covid19_epsg3035.shp", 
  delete_layer = TRUE
)